USE [DBSwindles]
GO
/****** Object:  Table [dbo].[tblCUSTOMER]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE lower(name) = 'tblcustomer') 

BEGIN
	CREATE TABLE [dbo].[tblCUSTOMER](
		[CustomerID] [int] IDENTITY(1,1) NOT NULL,
		[CustomerNumber] [varchar](50) NULL,
		[CustomerName] [varchar](100) NULL,
		[CreditRating] [int] NULL,
		[Reason] [varchar](1000) NULL,
		[Password] [varchar](50) NULL,
		[Email] [varchar](50) NULL
	 CONSTRAINT [PK_tblCUSTOMER] PRIMARY KEY([CustomerID])
	)
	PRINT 'tblCustomer created'
END
ELSE 
	PRINT 'tblCustomer exists'

GO

/****** Object:  Table [dbo].[tblUSER]    ******/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE lower(name) = 'tbluser') 

BEGIN
	CREATE TABLE [dbo].[tblUSER](
		[UserID] [int] IDENTITY(1,1) NOT NULL,
		[NameFirst] [varchar](50) NULL,
		[NameFamily] [varchar](50) NULL,
		[UserName] [varchar](50) NULL,
		[Password] [varchar](50) NULL,
		[UserRole] [varchar](50) NULL,
		[SwindlesID] [varchar] (50) NULL,
	 CONSTRAINT [PK_tblUSER] PRIMARY KEY ([UserID])
	)
	PRINT 'tblUser created'
END
ELSE 
	PRINT 'tblUser exists'
GO

/****** Object:  Table [dbo].[tblQUOTE]    ******/

IF NOT EXISTS (SELECT * FROM sys.tables WHERE lower(name) = 'tblquote') 

BEGIN
CREATE TABLE [dbo].[tblQUOTE](
	[QuoteID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NULL,
	[PrptyType] [varchar](50) NULL,
	[PrptySize] [decimal](18, 2) NULL,
	[PrptyYearBuilt] [int] NULL,
	[PrptyAddressLine1] [varchar](100) NULL,
	[PrptyAddressLine2] [varchar](100) NULL,
	[PrptySuburb] [varchar](50) NULL,
	[PrptyState] [varchar](50) NULL,
	[PrptyPostcode] [int] NULL,
	[PrptyValue] [decimal](18, 2) NULL,
	[LoanAmount] [decimal](18, 2) NULL,
	[LoanTerm] [int] NULL,
	[LoanIntrest] [decimal](18, 2) NULL,
	[LoanInsurance] [nchar](50) NULL,
	[LoanMRepayment] [decimal](18, 2) NULL,
	[QuoteDate] [datetime] NULL,
	[QuoteNumber] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblQUOTE] PRIMARY KEY ([QuoteID])
)
	PRINT 'tblQuote created'
END
ELSE 
	PRINT 'tblQuote exists'
GO


/****** Object:  Table [dbo].[tblAPPLICATION]    ******/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE lower(name) = 'tblapplication') 

BEGIN
	CREATE TABLE [dbo].[tblAPPLICATION](
		[ApplicationID] [int] IDENTITY(1,1) NOT NULL,
		[CustomerID] [int] NULL,
		[QuoteID] [int] NULL,
		[AssetValue] [decimal](18, 2) NULL,
		[AnnualIncome] [decimal](18, 2) NULL,
		[AnnualExpense] [decimal](18, 2) NULL,
		[ApplicationStatus] [varchar](50) NULL,
		[StatusUpdateDate] [datetime] NULL,
		[StatusUpdateUserID] [int] NULL,
		[Comments] [varchar](1000) NULL,
	 CONSTRAINT [PK_tblAPPLICATION] PRIMARY KEY ([ApplicationID])
	)
	PRINT 'tblApplication created'
END
ELSE 
	PRINT 'tblApplication exists'

GO